﻿package org.hyy.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import org.hyy.base.DatabaseConnection;
import org.hyy.vo.Category;

public class CategoryDAO {
	private DatabaseConnection dbc;
	private Connection con;				//数据库连接对象
	private PreparedStatement ps;		//预编译对象，用来执行SQL语句
	private ResultSet rs;

	public CategoryDAO(){
		
	}
	
	/**
	 * 数据封装方法
	 * @param rs
	 * @return
	 */
	private Category getCategoryInfo(ResultSet rs){
		Category category = null;
		category = new Category();
		try {
			category.setId(rs.getInt("id"));
			category.setPid(rs.getInt("pid"));
			category.setName(rs.getString("name"));
			category.setDescr(rs.getString("descr"));
			category.setCno(rs.getInt("cno"));
			category.setGrade(rs.getInt("grade"));
			return category;
		} catch (SQLException e) {
			
			e.printStackTrace();
			return null;
		}
		
	}
	
	/**
	 * 获取根类别
	 * @return
	 */
	public List<Category> GetRootCatergory(){
		//声明根类别链表
		List<Category> rootCatergory = null;
		//打开数据库
		dbc = new DatabaseConnection();
		con = dbc.getCon();
		//操作数据库
		String sql = "select * from category where grade = 1";
		try {
			ps = con.prepareStatement(sql);
			
			//执行操作
			rs = ps.executeQuery();
			while(rs.next()){
				Category c = this.getCategoryInfo(rs);
				rootCatergory.add(c);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			//关闭数据库
			dbc.close(con);
			dbc = null;
		}
		
		return rootCatergory;
	}
	
	/**
	 * 显示所有类别
	 * @return
	 */
	public List<Category> GetCatergories(){
		//声明根类别链表
		List<Category> categories = null;
		//打开数据库
		dbc = new DatabaseConnection();
		con = dbc.getCon();
		//操作数据库
		String sql = "select * from category order by cno";
		try {
			ps = con.prepareStatement(sql);
			
			//执行操作
			rs = ps.executeQuery();
			categories = new ArrayList<Category>();
			while(rs.next()){
				Category c = this.getCategoryInfo(rs);
				categories.add(c);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			//关闭数据库
			dbc.close(con);
			dbc = null;
		}
		
		return categories;
	}
	
	/**
	 * 插入子类别
	 * @param c
	 */
	public void addCategory(Category c){
		//打开数据库
		dbc = new DatabaseConnection();
		con = dbc.getCon();
		//操作数据库
		String sql = "insert into category values (null, ?, ?, ?, ?, ?)";
		try {
			ps = con.prepareStatement(sql);
			int cno = getLast(con,c);
			
			ps.setInt(1, c.getPid());
			ps.setString(2, c.getName());
			ps.setString(3, c.getDescr());
			ps.setInt(4, cno);
			ps.setInt(5, c.getGrade());
			
			//执行操作
			ps.executeUpdate();
			con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			//关闭数据库
			dbc.close(con);
			dbc = null;
		}
	}
	
	/**
	 * 获得要插入类别的最后一个子类
	 * @param conn
	 * @param c
	 * @return
	 */
	public int getLast(Connection conn,Category c){
		int cno = -1;
		
		String sqlMax = "select max(cno) from category where pid = " + c.getPid();
		
		Statement stmtMax = getStatement(conn);
		ResultSet rsMax = getResultSet(stmtMax, sqlMax);
		
		try {
			rsMax.next();
			int cnoMax = rsMax.getInt(1);		
			int baseNumber = (int)Math.pow(10, Category.LEVEL_LENGTH);
			//构建cstr要加上的数字
			int numberToAdd = (int)Math.pow(baseNumber, Category.MAX_GRADE - c.getGrade());
			
			if(cnoMax == 0) { //要加入的节点是该父亲下面的第一个子节点
				if(c.getPid() == 0) { //最顶层节点
					cno = numberToAdd;
				} else { //其他层节点
					int parentCno = getParentCno(conn, c); //拿到父亲代表串
					cno = parentCno + numberToAdd;
				}
			} else { //加入的节点不是该父亲下面的第一个
				cno = cnoMax + numberToAdd;
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			
		}
			
		return cno;
	}
	
	public static Statement getStatement(Connection conn) {
		Statement stmt = null; 
		try {
			if(conn != null) {
				stmt = conn.createStatement();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return stmt;
	}
	
	public static ResultSet getResultSet(Statement stmt, String sql) {
		ResultSet rs = null;
		try {
			if(stmt != null) {
				rs = stmt.executeQuery(sql);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}
	
	/**
	 * 获得父类别的 cno值
	 * @param conn
	 * @param child
	 * @return
	 */
	private int getParentCno(Connection conn, Category child){
		//打开数据库
		dbc = new DatabaseConnection();
		con = dbc.getCon();
		PreparedStatement pst;
		//操作数据库
		String sql = "select cno from category where id = ?";
		int cno = -1;
		try {
			pst = con.prepareStatement(sql);
			
			//参数赋值
			pst.setInt(1, child.getPid());
			
			//执行操作
			rs = pst.executeQuery();
			rs.next();
			cno = rs.getInt(1);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			
		}
		return cno;
	}
	
	public Category findById(int id){
		Category category = null;
		//打开数据库
		dbc = new DatabaseConnection();
		con = dbc.getCon();
		//操作数据库
		String sql = "select * from category where id = ?";
		try {
			ps = con.prepareStatement(sql);
			
			//参数赋值
			ps.setInt(1, id);
			
			//执行操作
			rs = ps.executeQuery();
			rs.next();
			category = new Category();
			category = this.getCategoryInfo(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			//关闭数据库
			dbc.close(con);
			dbc = null;
		}
		return category;
	}
	
	public void change(int id,String name,String descr){
		//打开数据库
		dbc = new DatabaseConnection();
		con = dbc.getCon();
		//操作数据库
		String sql = "update category set name = ? , descr = ? where id = ?";
		try {
			ps = con.prepareStatement(sql);
			
			//参数赋值
			ps.setString(1, name);
			ps.setString(2, descr);
			ps.setInt(3, id);
			
			//执行操作
			ps.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			//关闭数据库
			dbc.close(con);
			dbc = null;
		}
	}
	
	public void add(Category c){
		//打开数据库
		dbc = new DatabaseConnection();
		con = dbc.getCon();
		//操作数据库
		String sql = "insert into category values (null, ?, ?, ?, ?, ?)";
		try {
			ps = con.prepareStatement(sql);
			int cno = getLast(con,c);
			System.out.print(cno);
			
			//参数赋值
			ps.setInt(1, c.getPid());
			ps.setString(2, c.getName());
			ps.setString(3, c.getDescr());
			ps.setInt(4, cno);
			ps.setInt(5, c.getGrade());
			
			//执行操作
			ps.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			//关闭数据库
			dbc.close(con);
			dbc = null;
		}
	}
	
}
